package com.lyq.bean.mySql;

import com.lyq.bean.Course;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CourseDAO {

    // 根据专业和届数获取必修课程
    public static List<Course> getRequiredCoursesByMajorAndYear(Integer majorId, Integer yearId) {
        List<Course> courses = new ArrayList<>();
        StringBuilder sql = new StringBuilder(
                "SELECT c.*, m.major_name as majorName, y.enrollment_year as yearName " +
                        "FROM course c " +
                        "LEFT JOIN majors m ON c.major_id = m.major_id " +
                        "LEFT JOIN academic_years y ON c.year_id = y.year_id " +
                        "WHERE c.is_required = 1"
        );

        // 添加筛选条件
        if (majorId != null) {
            sql.append(" AND c.major_id = ?");
        }
        if (yearId != null) {
            sql.append(" AND c.year_id = ?");
        }

        try  {
            (new MysqlLinking()).getLink();
            Connection conn = MysqlLinking.conn;
            PreparedStatement pstmt = conn.prepareStatement(sql.toString());

            int paramIndex = 1;
            if (majorId != null) {
                pstmt.setInt(paramIndex++, majorId);
            }
            if (yearId != null) {
                pstmt.setInt(paramIndex++, yearId);
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Course course = new Course();
                    course.setNo(rs.getString("C_No"));
                    course.setName(rs.getString("C_Name"));
                    course.setCredit(rs.getString("C_Credit"));
                    course.setMajorId(rs.getInt("major_id"));
                    course.setYearId(rs.getInt("year_id"));
                    course.setMajorName(rs.getString("majorName"));
                    course.setYearName(rs.getString("yearName"));
                    courses.add(course);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return courses;
    }

    // 获取所有必修课程
    public static List<Course> getAllRequiredCourses() {
        List<Course> courses = new ArrayList<>();
        String sql = "SELECT c.*, m.major_name as majorName, y.enrollment_year as yearName " +
                "FROM course c " +
                "LEFT JOIN majors m ON c.major_id = m.major_id " +
                "LEFT JOIN academic_years y ON c.year_id = y.year_id " +
                "WHERE c.is_required = 1";

        try  {
            (new MysqlLinking()).getLink();
            Connection conn = MysqlLinking.conn;
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Course course = new Course();
                course.setNo(rs.getString("C_No"));
                course.setName(rs.getString("C_Name"));
                course.setCredit(rs.getString("C_Credit"));
                course.setMajorId(rs.getInt("major_id"));
                course.setYearId(rs.getInt("year_id"));
                course.setMajorName(rs.getString("majorName"));
                course.setYearName(rs.getString("yearName"));
                courses.add(course);
                System.out.println(course);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return courses;
    }
}
